package com.ruoyi.wemedia.util;

import org.redisson.misc.Hash;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class UpdateImg {
    public static void main(String[] args) throws Exception{

        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://192.168.0.80:3306/ry_news_wemedia?characterEncoding=UTF-8",
            "root",
            "root");
        //查询关系表
        PreparedStatement pstmt =  conn.prepareStatement("select wnm.news_id as news_id,wm.url as url from wm_news_material as wnm,wm_material as wm where wnm.material_id = wm.id");
        ResultSet rs = pstmt.executeQuery();
        ArrayList<HashMap> mapList = new ArrayList<>();
        HashSet<Long> idsSet = new HashSet<>();
        while(rs.next()) {
            HashMap<String,Object> map = new HashMap<>();
            map.put("url",rs.getString("url"));
            map.put("news_id",rs.getLong("news_id"));
            idsSet.add((Long)map.get("news_id"));
            //System.out.println(map);
            mapList.add(map);

        }

        //合并多个图片
        ArrayList<HashMap> newsList = new ArrayList<>();
        for(Long id : idsSet){
            String url = "";

            for(HashMap map : mapList){

                //取出新闻id
                Long news_id = (Long)map.get("news_id");

                if(id == news_id || id.equals(news_id)){
                    //System.out.println(news_id);
                    url = url + map.get("url") + ",";
                }
            }
            //去掉最后一个逗号
            if(url.length() > 0 && url.indexOf(",") != -1) url = url.substring(0,url.length() -1);

            HashMap newsMap1 = new HashMap();
            newsMap1.put("news_id",id);
            newsMap1.put("images",url);
            newsList.add(newsMap1);
            //System.out.println(newsMap1);
        }
        for(HashMap map : newsList){
            pstmt =  conn.prepareStatement("update wm_news set images = ? where id = ?");
            pstmt.setString(1,(String)map.get("images"));
            pstmt.setLong(2,(Long)map.get("news_id"));
            pstmt.executeUpdate();
        }

        pstmt.close();
        rs.close();

        conn.close();

    }

    public static void ap_article() throws  Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://192.168.0.80:3306/ry_news_article?characterEncoding=UTF-8",
            "root",
            "root");
        PreparedStatement pstmt =  conn.prepareStatement("select * from ap_article");
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Long> ids = new ArrayList<>();
        while(rs.next()) {
            ids.add(rs.getLong("id"));
        }
        rs.close();
        Random random = new Random();
        for(Long id : ids){
            String url = imgUrls[random.nextInt(19)];
            pstmt =  conn.prepareStatement("update ap_article set images = ? where id = ?");
            pstmt.setString(1,url);
            pstmt.setLong(2,id);
            pstmt.executeUpdate();
        }

        pstmt.close();
        conn.close();
    }
    public static void updateNews()throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://192.168.0.80:3306/ry_news_wemedia?characterEncoding=UTF-8",
            "root",
            "root");
        PreparedStatement pstmt =  conn.prepareStatement("select * from wm_news");
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Long> ids = new ArrayList<>();
        while(rs.next()) {
            ids.add(rs.getLong("id"));
        }
        rs.close();
        Random random = new Random();
        for(Long id : ids){
            String url = imgUrls[random.nextInt(19)];
            pstmt =  conn.prepareStatement("update wm_news set images = ? where id = ?");
            pstmt.setString(1,url);
            pstmt.setLong(2,id);
            pstmt.executeUpdate();
        }

        pstmt.close();
        conn.close();
    }
    public static void updateMaterial() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://192.168.0.80:3306/ry_news_wemedia?characterEncoding=UTF-8",
            "root",
            "root");
        PreparedStatement pstmt =  conn.prepareStatement("select * from wm_material");
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Long> ids = new ArrayList<>();
        while(rs.next()) {
            ids.add(rs.getLong("id"));
        }
        rs.close();

        Random random = new Random();
        for(Long id : ids){
            String url = imgUrls[random.nextInt(19)];
            pstmt =  conn.prepareStatement("update wm_material set url = ? where id = ?");
            pstmt.setString(1,url);
            pstmt.setLong(2,id);
            pstmt.executeUpdate();
        }

        pstmt.close();
        conn.close();
    }

    public static String[] imgUrls = {
        "http://192.168.0.80:9000/ruoyi/2022/10/11/782f447b7328431b9f51cfce24b7452a.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/84e5c835062f4ebda7f69d1a9dca590b.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/32d6a2bec8d74d0e9a1c35ef23e3e3a4.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/e4d483f195c3470c8a06ac439839e80a.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/0ee1c77146734631bc7c28c0d69b27a2.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/fa6197efcaf549b88a2c2bd7a911eb72.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/ecef3c9a28a34d8fac607b873bbe2e25.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/aa89ec5eb8ee4590bf2dedb8cfb30d43.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/cb629e8b434640a2a56cdee0ee037864.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/0a4599549eba4c32b50d948961841704.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/ad6abbf314a443cdb760ef93d258ff97.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/b2a5207a72f643b785ae8c71ec15b0d5.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/af731abbf07e4d579956ea2d6f175100.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/10630b079b5a4b54b6437f3a87a1bf67.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/56f809c1ed0a4100b2e2d6e5010d3031.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/9f03c2def42d47b8a31b13d418235a30.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/c9b9050e70d0432cbdf6e6b056892003.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/8f42893613f34958ba5ab9ff1a632e84.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/423dca0491ee4b5c950608f71bad160e.jpg",
        "http://192.168.0.80:9000/ruoyi/2022/10/14/eb239d8084ff4848a618778584df27a7.jpg"
    };
}
